#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/9/21 10:03
# @Author : 闫峰
# @File    : ceshi1.py
import json
from datetime import datetime

import pymongo

from utils.mysqlUtils.mongodb_utils import MongoDBLink
import os

from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.sql_server_utils import MSSQL

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])
info_path = ensure_path_sep(f"\\logs\\info-{dis_path}.log")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}.log")
if os.path.exists(info_path):
    os.remove(info_path)
if os.path.exists(error_path):
    os.remove(error_path)
if os.path.exists(warning_path):
    os.remove(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class TestChange:
    mapping = {
        "packageLength": "Length",
        "packageWidth": "Width",
        "packageHeight": "Height"
    }

    def test_allocation_bill(self):
        erp_db = MSSQL(ensure_path_sep("\\config\\config.yaml"), "sqlserver_db_erp_base")
        mysql_db = Database(ensure_path_sep("\\testcases\\erp\\responsible_inventory\\config.yaml"),
                            "irobotbox_datacenter")
        insert_db = Database(ensure_path_sep("\\config\\ceshi_mysql_config.yaml"), "log_db")
        # 遍历RB_Inventory_DetailLog库，验证allocation_bill
        get_BillNumber = """
        SELECT TOP 5000 BillNumber,OriginBillNumber,WareHouseID,SKU,id,CustomerID,AddTime,BillType 
        FROM [dbo].[RB_Inventory_DetailLog] 
        
        WHERE AddTime >= '2023-08-01 00:00:00' AND AddTime<='2023-08-31 23:59:59' AND CustomerID=1
        and id > %s order by id
        """

        id_offset = 0
        count = 0
        while True:
            res = erp_db.ExecQuery(get_BillNumber, (id_offset,))


            if res:
                id_offset = res[-1][4]
                # 过滤得到需要同步的数据
                # res = list(filter(
                #     lambda x: x[5] == 1 and x[6] >= datetime.strptime("2023-01-01", '%Y-%m-%d') and x[
                #         6] <= datetime.strptime("2023-09-21", '%Y-%m-%d'), res))
                # 如果数据存在
                if res:
                    self.handler_data(insert_db, res)
                    count += len(res)
                    INFO.logger.info(f"验证数据量为{count}")
                else:
                    continue
            else:
                break

    def handler_data(self, insert_db, res):

        # res = list(map(lambda x: (x[0],x[1],x[2],x[3]),res))
        allocation_bill_data = list(filter(lambda x: x[7] in [4, 5], res))
        if allocation_bill_data:
            self.insert_allocation_bill_data(insert_db, allocation_bill_data)
        inventory_check_order_data = list(filter(lambda x: x[7] in [6, 7], res))
        if inventory_check_order_data:
            self.insert_inventory_check_order_data(insert_db, inventory_check_order_data)
        other_order_data = list(filter(lambda x: x[7] in [1, 3], res))
        if other_order_data:
            self.insert_other_order_data(insert_db, other_order_data)
        procurement_putin_data = list(filter(lambda x: x[7] == 0, res))
        if procurement_putin_data:
            self.insert_procurement_putin_data(insert_db, procurement_putin_data)
        sales_putout_data = list(filter(lambda x: x[7] == 2, res))
        if sales_putout_data:
            self.insert_sales_putout_data(insert_db, sales_putout_data)
        supplier_returns_order = list(filter(lambda x: x[7] == 8, res))
        if supplier_returns_order:
            self.insert_supplier_returns_order(insert_db, supplier_returns_order)


    def insert_allocation_bill_data(self, insert_db, allocation_bill_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.allocation_bill (BillNumber,AllocationOrderNo,ToWarehouseId,SKU) VALUES (%s,%s,%s,%s);
        """
        allocation_bill_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), allocation_bill_data))
        rows = insert_db.execute_many(insert_sql, allocation_bill_data)
        if rows == 0:
            ERROR.logger.error(f"""INSERT IGNORE INTO zhcxkj_datacenter.allocation_bill 
            (BillNumber,AllocationOrderNo,ToWarehouseId,SKU) VALUES (%s,%s,%s,%s);插入失败
            data = {allocation_bill_data}
                """)

    def insert_inventory_check_order_data(self, insert_db, inventory_check_order_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.inventory_check_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
        """
        inventory_check_order_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), inventory_check_order_data))
        rows = insert_db.execute_many(insert_sql, inventory_check_order_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.inventory_check_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {inventory_check_order_data}
                """)

    def insert_other_order_data(self, insert_db, other_order_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.other_order (CustomerId,BillNumber,OrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        other_order_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), other_order_data))
        rows = insert_db.execute_many(insert_sql, other_order_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.other_order (CustomerId,BillNumber,OrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {other_order_data}
                """)

    def insert_procurement_putin_data(self, insert_db, procurement_putin_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.procurement_putin (CustomerId,BillNumber,VerificationOrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        procurement_putin_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), procurement_putin_data))
        rows = insert_db.execute_many(insert_sql, procurement_putin_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.procurement_putin (CustomerId,BillNumber,VerificationOrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {procurement_putin_data}
                """)

    def insert_sales_putout_data(self, insert_db, sales_putout_data):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.sales_putout (CustomerId,BillNumber,ClientOrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        sales_putout_data = list(map(lambda x: (x[0], x[1], x[2], x[3]), sales_putout_data))
        rows = insert_db.execute_many(insert_sql, sales_putout_data)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.sales_putout (CustomerId,BillNumber,ClientOrderNo,WareHouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {sales_putout_data}
                """)

    def insert_supplier_returns_order(self, insert_db, supplier_returns_order):
        insert_sql = """
        INSERT IGNORE INTO znkdceshilog.supplier_returns_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);

        """
        supplier_returns_order = list(map(lambda x: (x[0], x[1], x[2], x[3]), supplier_returns_order))
        rows = insert_db.execute_many(insert_sql, supplier_returns_order)
        if rows == 0:
            ERROR.logger.error(f"""
            INSERT IGNORE INTO znkdceshilog.supplier_returns_order (CustomerId,BillNumber,OrderNo,WarehouseId,SKU) VALUES (1,%s,%s,%s,%s);
            插入失败
            data = {supplier_returns_order}
                """)


if __name__ == '__main__':
    TestChange().test_allocation_bill()
